package top.my.db;

import io.vertx.core.AbstractVerticle;
import io.vertx.core.MultiMap;
import io.vertx.core.Vertx;
import io.vertx.mysqlclient.MySQLConnectOptions;
import io.vertx.mysqlclient.MySQLPool;
import io.vertx.sqlclient.PoolOptions;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import org.apache.log4j.Logger;
import top.my.Const;
import top.my.QueryException;
import top.my.bean.Emoji;
import top.my.bean.Emoji2;
import top.my.bean.User;
import top.my.utils.DBHelper;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class SqlServer extends AbstractVerticle {
    final Logger logger = Logger.getLogger(SqlServer.class);
    public MySQLPool client;
    public static SqlServer sqlServer;

    public SqlServer() {
        sqlServer = this;
    }

    private static long time;

    public static void main(String[] args) {
        Vertx vertx = Vertx.vertx();
        vertx.deployVerticle(new SqlServer());
    }

    @Override
    public void start() {
        connectClient();
        //testUpdate();
        //testQuery();
        //testInsert();
        //testDelete();
    }

    public void stop() {
        if (client != null) {
            client.close();
        }
    }

    private void connectClient() {
        PoolOptions poolOptions = new PoolOptions().setMaxSize(5);
        MySQLConnectOptions connectOptions = new MySQLConnectOptions()
                .setPort(3306)
                //.setHost("47.112.239.250")
                .setHost("127.0.0.1")
                .setDatabase("emoji")
                .setUser("root")
                .setPassword("root");
        //.setPassword("qwe27925383");
        logger.info("MySql启动");
        client = MySQLPool.pool(vertx, connectOptions, poolOptions);
    }

    /**
     * 插入一条数据
     *
     * @param tableName 表名
     * @param map       请求参数
     * @param callback  回调
     */
    public void insertBy(String tableName, MultiMap map, QueryRowCallBack callback) {
        String[] columns = new String[map.size()];
        String[] values = new String[map.size()];
        for (int i = 0; i < map.entries().size(); i++) {
            Map.Entry<String, String> entry = map.entries().get(i);
            columns[i] = entry.getKey();
            values[i] = entry.getValue();
        }
        String sql = DBHelper.insert(tableName, columns, values);
        operationBy(sql, "插入", tableName, map, null, null, callback);
    }

    /**
     * 删除一条数据
     *
     * @param tableName 表名
     * @param map       请求参数
     * @param callback  回调
     */
    public void deleteBy(String tableName, MultiMap map, QueryRowCallBack callback) {
        int size = map.entries().size();
        String[] columns = new String[size];
        String[] values = new String[size];
        for (int i = 0; i < size; i++) {
            Map.Entry<String, String> entry = map.entries().get(i);
            columns[i] = entry.getKey();
            values[i] = entry.getValue();
        }
        String sql = DBHelper.delete(tableName, columns, values);
        client.query(sql).execute(ar -> {
            if (ar.succeeded()) {
                RowSet<Row> resultRows = ar.result();
                System.out.println("删除单条.rowCount==>" + resultRows.rowCount());
                if (resultRows.rowCount() > 0) {
                    callback.onComplete(resultRows, null);
                } else {
                    callback.onComplete(null, new QueryException("删除失败", Const.REP_FAIL));
                }
            } else {
                System.out.println("Failure: " + ar.cause().getMessage());
                callback.onComplete(null, new QueryException("删除操作错误", Const.REP_ERROR));
            }
        });
    }

    /**
     * 更新一条数据
     *
     * @param tableName   表名
     * @param map         请求参数
     * @param searchKey   更新的字段
     * @param searchValue 更新的值
     * @param callback    回调
     */
    public void updateBy(String tableName, MultiMap map, String searchKey, String searchValue, QueryRowCallBack callback) {
        String[] columns = new String[map.size()];
        String[] values = new String[map.size()];
        for (int i = 0; i < map.entries().size(); i++) {
            Map.Entry<String, String> entry = map.entries().get(i);
            columns[i] = entry.getKey();
            values[i] = entry.getValue();
        }
        String sql = DBHelper.update(tableName, columns, values, searchKey, searchValue);
        operationBy(sql, "更新", tableName, map, searchKey, searchValue, callback);
    }

    /**
     * 操作
     *
     * @param sql       查询语句
     * @param operation 操作
     * @param tableName 表名
     * @param map       参数
     * @param callback  回调
     */
    public void operationBy(String sql, String operation, String tableName, MultiMap map, String searchKey, String searchValue, QueryRowCallBack callback) {
        client.query(sql).execute(ar -> {
            if (ar.succeeded()) {
                RowSet<Row> resultRows = ar.result();
                logger.info(operation + "单条.rowCount==>" + resultRows.rowCount());
                if (resultRows.rowCount() > 0) {
                    if (searchKey != null && searchValue != null) {
                        queryBy(tableName, searchKey, searchValue, callback);
                    } else {
                        queryBy(tableName, map, callback);
                    }
                } else {
                    callback.onComplete(null, new QueryException(operation + "失败", Const.REP_FAIL));
                }
            } else {
                logger.info("Failure: " + ar.cause().getMessage());
                if (ar.cause().getMessage().contains("Duplicate entry")) {//唯一值重复,如注册账号不能重复插入相同账号
                    callback.onComplete(null, new QueryException(operation + "失败", Const.REP_FAIL));
                } else {
                    callback.onComplete(null, new QueryException(operation + "操作错误", Const.REP_ERROR));
                }
            }
        });
    }

    /**
     * 查询一条数据
     *
     * @param tableName 表名
     * @param map       请求参数
     * @param callback  回调
     */
    public void queryBy(String tableName, MultiMap map, QueryRowCallBack callback) {
        String[] columns = new String[map.size()];
        String[] values = new String[map.size()];
        String[] operations = new String[map.size()];
        for (int i = 0; i < map.entries().size(); i++) {
            Map.Entry<String, String> entry = map.entries().get(i);
            columns[i] = entry.getKey();
            values[i] = entry.getValue();
            operations[i] = " = ";
        }
        String sql = DBHelper.query(tableName, columns, operations, values);
        queryBy(sql, callback);
    }

    /**
     * 查询一条数据
     *
     * @param tableName 表名
     * @param column    查询的列
     * @param value     查询的值
     * @param callback  回调
     */
    public void queryBy(String tableName, String column, Object value, QueryRowCallBack callback) {
        String sql = DBHelper.query(tableName, column, value);
        queryBy(sql, callback);
    }

    /**
     * 查询一条数据
     *
     * @param sql      查询语句
     * @param callback 回调
     */
    public void queryBy(String sql, QueryRowCallBack callback) {
        client.query(sql).execute(ar -> {
            if (ar.succeeded()) {
                RowSet<Row> resultRows = ar.result();
                logger.info("查询单条.size==>" + resultRows.size() + "\n" + resultRows.columnsNames());
                if (resultRows.size() > 0) {
                    callback.onComplete(resultRows, null);
                } else {
                    callback.onComplete(null, new QueryException("查询不存在", Const.REP_FAIL_NONE));
                }
            } else {
                logger.info("Failure: " + ar.cause().getMessage());
                callback.onComplete(null, new QueryException("查询错误", Const.REP_ERROR));
            }
        });
    }

    public <T> void query(String tableName, T obj, QueryCallBack<T> callback) {
        List<String> columnList = new ArrayList<>();
        List<String> valueList = new ArrayList<>();
        List<String> operationList = new ArrayList<>();
        if (DBHelper.reflectObject(columnList, valueList, obj)) {
            for (int i = 0; i < columnList.size(); i++) {
                operationList.add(" = ");
            }
            query(tableName, columnList, operationList, valueList, callback);
        }
    }

    public <T> void query(String tableName, List<String> columnList, List<String> operationList, List<String> valueList, QueryCallBack<T> callback) {
        String[] columns = columnList.toArray(new String[columnList.size()]);
        String[] values = valueList.toArray(new String[valueList.size()]);
        String[] operations = operationList.toArray(new String[operationList.size()]);
        query(tableName, columns, operations, values, callback);
    }

    public <T> void query(String tableName, String[] columns, String[] operations, String[] values, QueryCallBack<T> callback) {
        String sql = DBHelper.query(tableName, columns, operations, values);
        client.query(sql).execute(ar -> {
            if (ar.succeeded()) {
                RowSet<Row> resultRows = ar.result();
                System.out.println("Got " + resultRows.size() + " rows ");
                if (resultRows.size() > 0) {
                    logger.info("查询单条.size==>" + resultRows.size() + "\n" + resultRows.columnsNames());
                    Row nextItem = resultRows.iterator().next();
                    try {
                        Class<T> clazz = DBHelper.getGenericsClass(callback);
                        T obj = clazz.newInstance();
                        Field[] fields = clazz.getDeclaredFields();
                        for (Field field : fields) {
                            if (nextItem.getColumnIndex(field.getName()) != -1) {
                                field.setAccessible(true);
                                field.set(obj, nextItem.getValue(field.getName()));
                            }
                        }
                        callback.onComplete(obj);
                        return;
                    } catch (IllegalAccessException | InstantiationException e) {
                        e.printStackTrace();
                    }
                }
            } else {
                logger.info("Failure: " + ar.cause().getMessage());
            }
            callback.onComplete(null);
        });
    }

    public <T> void query(String tableName, String column, Object value, QueryCallBack<T> callback) {
        String sql = DBHelper.query(tableName, column, value);
        client.query(sql).execute(ar -> {
            if (ar.succeeded()) {
                RowSet<Row> resultRows = ar.result();
                logger.info("查询单条.size==>" + resultRows.size() + "\n" + resultRows.columnsNames());
                if (resultRows.size() > 0) {
                    Row nextItem = resultRows.iterator().next();
                    try {
                        Class<T> clazz = DBHelper.getGenericsClass(callback);
                        T obj = clazz.newInstance();
                        Field[] fields = clazz.getDeclaredFields();
                        for (Field field : fields) {
                            if (nextItem.getColumnIndex(field.getName()) != -1) {
                                field.setAccessible(true);
                                field.set(obj, nextItem.getValue(field.getName()));
                            }
                        }
                        callback.onComplete(obj);
                        return;
                    } catch (IllegalAccessException | InstantiationException e) {
                        e.printStackTrace();
                    }
                }
            } else {
                logger.info("Failure: " + ar.cause().getMessage());
            }
            callback.onComplete(null);
        });
    }

    /**
     * @param tableName   表名
     * @param obj         修改实体类
     * @param searchKey   查询的字段
     * @param searchValue 查询的值
     * @return
     */
    public <T> void update(String tableName, T obj, String searchKey, String searchValue, QueryCallBack<T> callback) {
        List<String> columnList = new ArrayList<>();
        List<String> valueList = new ArrayList<>();
        if (DBHelper.reflectObject(columnList, valueList, obj)) {
            update(tableName, columnList, valueList, searchKey, searchValue, callback);
        }
    }

    /**
     * @param tableName   表名
     * @param columnList  修改的字段列表
     * @param valueList   修改的值列表
     * @param searchKey   查询的字段
     * @param searchValue 查询的值
     * @param callback
     * @param <T>
     */
    public <T> void update(String tableName, List<String> columnList, List<String> valueList, String searchKey, String searchValue, QueryCallBack<T> callback) {
        String[] columns = columnList.toArray(new String[columnList.size()]);
        String[] values = valueList.toArray(new String[valueList.size()]);
        update(tableName, columns, values, searchKey, searchValue, callback);
    }

    public <T> void update(String tableName, String[] columns, String[] values, String searchKey, String searchValue, QueryCallBack<T> callback) {
        String sql = DBHelper.update(tableName, columns, values, searchKey, searchValue);
        client.query(sql).execute(ar -> {
            if (ar.succeeded()) {
                RowSet<Row> resultRows = ar.result();
                //System.out.println("更新单条.rowCount==>" + resultRows.rowCount());
                if (resultRows.rowCount() > 0) {
                    //System.out.println("时间1: " + (System.currentTimeMillis()-time));
                    query(tableName, searchKey, searchValue, callback);
                    return;
                }
            } else {
                logger.info("Failure: " + ar.cause().getMessage());
            }
            callback.onComplete(null);
        });
    }

    public <T> void insert(String tableName, T obj, QueryCallBack<T> callback) {
        List<String> columnList = new ArrayList<>();
        List<String> valueList = new ArrayList<>();
        if (DBHelper.reflectObject(columnList, valueList, obj)) {
            insert(tableName, columnList, valueList, callback);
        }
    }

    public <T> void insert(String tableName, List<String> columnList, List<String> valueList, QueryCallBack<T> callback) {
        String[] columns = columnList.toArray(new String[columnList.size()]);
        String[] values = valueList.toArray(new String[valueList.size()]);
        insert(tableName, columns, values, callback);
    }

    public <T> void insert(String tableName, String[] columns, String[] values, QueryCallBack<T> callback) {
        String sql = DBHelper.insert(tableName, columns, values);
        client.query(sql).execute(ar -> {
            if (ar.succeeded()) {
                RowSet<Row> resultRows = ar.result();
                logger.info("插入条数.rowCount=" + resultRows.rowCount());
                if (resultRows.rowCount() > 0) {
                    int index = 0;
                    for (int i = 0; i < columns.length; i++) {
                        if (columns[i].equals("ct")) {
                            index = i;
                            break;
                        }
                    }
                    query(tableName, columns[index], values[index], callback);
                    return;
                }
            } else {
                logger.info("Failure: " + ar.cause().getMessage());
            }
            callback.onComplete(null);
        });
    }


    public <T> void delete(String tableName, T obj, QueryCallBack<Boolean> callback) {
        List<String> columnList = new ArrayList<>();
        List<String> valueList = new ArrayList<>();
        if (DBHelper.reflectObject(columnList, valueList, obj)) {
            delete(tableName, columnList, valueList, callback);
        }
    }

    public <T> void delete(String tableName, List<String> columnList, List<String> valueList, QueryCallBack<Boolean> callback) {
        String[] columns = columnList.toArray(new String[columnList.size()]);
        String[] values = valueList.toArray(new String[valueList.size()]);
        delete(tableName, columns, values, callback);
    }

    public void delete(String tableName, String column, String value, QueryCallBack<Boolean> callback) {
        delete(tableName, new String[]{column}, new String[]{value}, callback);
    }

    public void delete(String tableName, String[] columns, String[] values, QueryCallBack<Boolean> callback) {
        String sql = DBHelper.delete(tableName, columns, values);
        client.query(sql).execute(ar -> {
            if (ar.succeeded()) {
                RowSet<Row> resultRows = ar.result();
                logger.info("删除条数.rowCount=" + resultRows.rowCount());
                if (resultRows.rowCount() > 0) {
                    callback.onComplete(true);
                    return;
                }
            } else {
                logger.info("Failure: " + ar.cause().getMessage());
            }
            callback.onComplete(false);
        });
    }

    public static void main1(String[] args) throws Exception {
        // 常规方式
        Emoji2 student = new Emoji2();
        long startNormal = System.currentTimeMillis();
        for (int i = 0; i < 1000000; i++) {
            student.name = "hello";
        }
        System.out.println("timeNormal=" + (System.currentTimeMillis() - startNormal));

        //反射方式1
        long startReflection1 = System.currentTimeMillis();
        for (int i = 0; i < 100000000; i++) {
            Emoji student1 = new Emoji();
            Class<?> clzz = student1.getClass();
            Field field = clzz.getDeclaredField("name");
            field.setAccessible(true);
            field.set(student1, "hello");
        }
        System.out.println("timeReflection=" + (System.currentTimeMillis() - startReflection1));

        //反射方式2
        //Emoji2 student2 = new Emoji2();
        QueryCallBack<Emoji> callbakc = new QueryCallBack<Emoji>() {
            @Override
            public void onComplete(Emoji emoji) {
                System.out.println("Emoji: " + emoji);
            }
        };
        QueryCallBack<Emoji2> callbakc2 = new QueryCallBack<Emoji2>() {
            @Override
            public void onComplete(Emoji2 emoji) {
                System.out.println("Emoji: " + emoji);
            }
        };
        long startReflection = System.currentTimeMillis();
        Class<Emoji> claz = DBHelper.getGenericsClass(callbakc);
        for (int i = 0; i < 100000000; i++) {
            Emoji cla = claz.newInstance();
            Field field = claz.getDeclaredField("name");
            field.setAccessible(true);
            field.set(cla, "hello");
        }
        System.out.println("timeReflection=" + (System.currentTimeMillis() - startReflection));


        //Class<Emoji> claz = getGenericsClass(callbakc);
        //Class<Emoji> claz2 = getGenericsClass(callbakc2);

        //反射方式3
        /*Emoji student3 = new Emoji();
        Class<?> clzz3 = student3.getClass();
        long startReflection3 = System.currentTimeMillis();
        for (int i = 0; i < 10000000; i++) {
            Method method = clzz3.getDeclaredMethod("setName", String.class);
            method.invoke(clzz3.newInstance(), "hello");
        }
        System.out.println("timeReflection=" + (System.currentTimeMillis() - startReflection3));*/
    }


    public void testQuery() {
        SqlServer.sqlServer.query("emoji", "id", 72, new QueryCallBack<Emoji>() {
            @Override
            public void onComplete(Emoji emoji) {
                System.out.println("Emoji: " + emoji);
            }
        });

        /*client.query(" SELECT * FROM emoji").execute(ar -> {
            if (ar.succeeded()) {
                RowSet<Row> resultRows = ar.result();
                System.out.println("更新单条.rowCount==>" + resultRows.rowCount());
                System.out.println("更新单条.size==>" + resultRows.size());
            }else {
                System.out.println("Failure: " + ar.cause().getMessage());
            }
        });*/
    }

    public void testUpdate() {
        time = System.currentTimeMillis();
        Emoji emoUpdate = new Emoji();
        emoUpdate.setId(72);
        emoUpdate.setName("修改一儿2");
        emoUpdate.setMsg("https://v5.cdn.jiaonizuocai.com/video/202202/1013/y_rt2/6204a989ec505/eGg3MjBw.mp4?v=1.1&sign=cd2803df530ac485abd8c09cdfe41efd&t=62060da3");
        emoUpdate.setPic("food2.jpeg");
        emoUpdate.setSearch("玩具,");
        emoUpdate.setCt(System.currentTimeMillis());
        //for (int i = 0; i < 100000; i++) {
        //int finalI = i + 100;
        //emoUpdate.setName("天气不错" + finalI);
        SqlServer.sqlServer.update("emoji", emoUpdate, "id", "" + 72, new QueryCallBack<Emoji>() {
            @Override
            public void onComplete(Emoji emoji) {
                //if (finalI == 99999) {
                System.out.println("Emoji: " + emoji);
                System.out.println("时间: " + (System.currentTimeMillis() - time));
                //}
            }
        });
        //}
    }

    public void testUpdate2() {
        time = System.currentTimeMillis();
        Emoji emoUpdate = new Emoji();
        emoUpdate.setName("天气不错");
        emoUpdate.setMsg("https://v5.cdn.jiaonizuocai.com/video/202202/1013/y_rt2/6204a989ec505/eGg3MjBw.mp4?v=1.1&sign=cd2803df530ac485abd8c09cdfe41efd&t=62060da3");
        emoUpdate.setPic("food2.jpeg");
        emoUpdate.setSearch("玩具,");
        emoUpdate.setCt(System.currentTimeMillis());
        for (int i = 0; i < 100000; i++) {
            int finalI = i;
            String[] columns = new String[]{"id", "ct", "count", "name"};
            String[] values = new String[]{"102", "1661569596911", "0", "'天气不错12'"};

            String sql = DBHelper.update("emoji", columns, values, "id", "102");//+DBHelper.orderBy("id",true);
            SqlServer.sqlServer.client.query(sql).execute(ar -> {
                if (ar.succeeded()) {
                    RowSet<Row> resultRows = ar.result();
                    //System.out.println("更新单条.rowCount==>" + resultRows.rowCount());
                    if (resultRows.rowCount() > 0) {
                        if (finalI == 99999) {
                            System.out.println("时间1: " + (System.currentTimeMillis() - time));
                        }
                        SqlServer.sqlServer.query("emoji", "id", "102", new QueryCallBack<Emoji>() {
                            @Override
                            public void onComplete(Emoji emoji) {
                                if (finalI == 99999) {
                                    System.out.println(finalI + ">时间: " + (System.currentTimeMillis() - time));
                                }
                            }
                        });
                        return;
                    }
                } else {
                    System.out.println("Failure: " + ar.cause().getMessage());
                }
            });
        }
    }

    public void testInsert() {
        /*Emoji emoUpdate = new Emoji();
        emoUpdate.setName("天气不错");
        emoUpdate.setMsg("https://v5.cdn.jiaonizuocai.com/video/202202/1013/y_rt2/6204a989ec505/eGg3MjBw.mp4?v=1.1&sign=cd2803df530ac485abd8c09cdfe41efd&t=62060da3");
        emoUpdate.setPic("food2.jpeg");
        emoUpdate.setSearch("玩具,");
        emoUpdate.setCt(System.currentTimeMillis());
        SqlServer.sqlServer.insert("emoji", emoUpdate, new QueryCallBack<Emoji>() {
            @Override
            public void onComplete(Emoji emoji) {
                System.out.println("Emoji: " + emoji);
            }
        });*/
        User user = new User();
        user.setName("天气不错");
        user.setUsername("root");
        user.setPassword("123456");
        SqlServer.sqlServer.insert(Const.DB_USER, user, new QueryCallBack<User>() {
            @Override
            public void onComplete(User emoji) {
                System.out.println("AccountUser: " + emoji);
            }
        });
    }

    public void testDelete() {
        SqlServer.sqlServer.delete("emoji", "id", "74", new QueryCallBack<Boolean>() {
            @Override
            public void onComplete(Boolean success) {
                if (success) {
                    System.out.println("移除id=87成功");
                } else {
                    System.out.println("移除失败");
                }
            }
        });
    }

}
